package com.zr.gdms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.gdms.dao.ApplicationDao;
import com.zr.gdms.model.Application;
import com.zr.gdms.model.Teacher;
import com.zr.gdms.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
 * 申请表逻辑操作实现层
 * @author 李培宁
 *
 */
public class ApplicationDaoImpl implements ApplicationDao{

	@Override
	public JSONArray selectApplication(int page,int rows,String apptype) {
		
		JSONArray ja = new JSONArray();
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT application.appid,application.appstate,application.apptime,");
		sql.append(" application.appcontent,application.apptype,application.tid,teacher.tname");
		sql.append(" FROM application");
		sql.append(" INNER JOIN teacher");
		sql.append(" ON application.tid=teacher.tid");
		sql.append(" WHERE application.apptype=? LIMIT ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, apptype);
			pst.setInt(2, page);
			pst.setInt(3, rows);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				JSONObject j = new JSONObject();
				j.put("appid",rs.getInt("appid") );
				j.put("apptype", rs.getString("apptype"));
				j.put("appcontent",rs.getString("appcontent"));
				j.put("appstate",rs.getString("appstate"));
				j.put("apptime",rs.getString("apptime"));
				j.put("tid",rs.getString("tid"));
				j.put("tname",rs.getString("tname"));
				ja.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ja;
	}

	@Override
	public int findApplicationCount(String apptype) {
		int acount = 0;
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(application.appid) AS acount");
		sql.append(" FROM application where application.apptype = ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, apptype);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				acount = rs.getInt("acount");
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return acount;
	}

	@Override
	public boolean updateApplication(String appstate,String apptype) {
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE application SET application.appstate = ? WHERE application.apptype=?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst  = con.prepareStatement(sql.toString());
			pst.setString(1, appstate);
			pst.setString(2, apptype);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = true;
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
}
